{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Median Return"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 本段代码展示了利用quantOS系统提取数据，并计算指数年收益率及指数成份股年收益率中位数的具体步骤。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. 环境设置"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from jaqs.data import RemoteDataService\n",
    "from jaqs.data import DataView\n",
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 如果没有使用quantos金融终端，请自行替换phone,token内容\n",
    "import os\n",
    "phone = os.environ.get(\"QUANTOS_USER\")\n",
    "token = os.environ.get(\"QUANTOS_TOKEN\")\n",
    "\n",
    "data_config = {\n",
    "  \"timeout\": 1800,\n",
    "  \"remote.data.address\": \"tcp://data.quantos.org:8910\",\n",
    "  \"remote.data.username\":  phone,\n",
    "  \"remote.data.password\":  token}\n",
    "\n",
    "# 启动DataView，并设置用户名密码\n",
    "dv = DataView()\n",
    "ds = RemoteDataService()\n",
    "ds.init_from_config(data_config)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. 参数设置 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# -------------------------------------------------------------------------------\n",
    "# 设置待分析版块及分析起止日期\n",
    "# -------------------------------------------------------------------------------\n",
    "# 上证50， 沪深300，中证500，中证全指\n",
    "# indexList = ['000016.SH', '000300.SH', '000905.SH', '000985.CSI']\n",
    "indexList = ['000016.SH', '000300.SH', '000905.SH'] #'000985.CSI'\n",
    "retCompare = pd.DataFrame()\n",
    "startdate = 20151231\n",
    "enddate = 20171130"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. 分析步骤 (注：中证全指000985.CSI中成份股较多，此部分程序运行时间较长)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Initialize config success.\n",
      "Query data...\n",
      "Query data - query...\n",
      "NOTE: price adjust method is [post adjust]\n",
      "Query data - daily fields prepared.\n",
      "Query instrument info...\n",
      "Query adj_factor...\n",
      "Query benchmark...\n",
      "Query benchmar member info...\n",
      "Data has been successfully prepared.\n",
      "000016.SH\n",
      "Initialize config success.\n",
      "Query data...\n",
      "Query data - query...\n",
      "NOTE: price adjust method is [post adjust]\n",
      "249\n",
      "499\n",
      "249\n",
      "499\n",
      "Query data - daily fields prepared.\n",
      "Query instrument info...\n",
      "Query adj_factor...\n",
      "Query benchmark...\n",
      "Query benchmar member info...\n",
      "Data has been successfully prepared.\n",
      "000300.SH\n",
      "Initialize config success.\n",
      "Query data...\n",
      "Query data - query...\n",
      "NOTE: price adjust method is [post adjust]\n",
      "142\n",
      "285\n",
      "428\n",
      "142\n",
      "285\n",
      "428\n",
      "Query data - daily fields prepared.\n",
      "Query instrument info...\n",
      "Query adj_factor...\n",
      "Query benchmark...\n",
      "Query benchmar member info...\n",
      "Data has been successfully prepared.\n",
      "000905.SH\n",
      "Initialize config success.\n",
      "Query data...\n",
      "Query data - query...\n",
      "NOTE: price adjust method is [post adjust]\n",
      "31\n",
      "63\n",
      "95\n",
      "127\n",
      "159\n",
      "191\n",
      "223\n",
      "255\n",
      "287\n",
      "319\n",
      "351\n",
      "383\n",
      "415\n",
      "447\n",
      "479\n",
      "31\n",
      "63\n",
      "95\n",
      "127\n",
      "159\n",
      "191\n",
      "223\n",
      "255\n",
      "287\n",
      "319\n",
      "351\n",
      "383\n",
      "415\n",
      "447\n",
      "479\n",
      "Query data - daily fields prepared.\n",
      "Query instrument info...\n",
      "Query adj_factor...\n",
      "Query benchmark...\n",
      "Query benchmar member info...\n",
      "Data has been successfully prepared.\n",
      "000985.CSI\n"
     ]
    }
   ],
   "source": [
    "for index in indexList:\n",
    "\n",
    "    # 设置DataView\n",
    "    props = {'start_date': startdate, 'end_date': enddate, 'universe': index,\n",
    "             'fields': 'close_adj',\n",
    "             'freq': 1}\n",
    "    dv.init_from_config(props, data_api=ds)\n",
    "    dv.prepare_data()\n",
    "\n",
    "    print(index)\n",
    "\n",
    "    # 取出板块指数，版块成份股日收盘价及是否为成分股的信息\n",
    "    closeData = dv.get_ts('close_adj')\n",
    "    closeData['trade_date'] = closeData.index\n",
    "    closeData['trade_date'] = closeData['trade_date'].apply(lambda x: str(x))\n",
    "    closeData['trade_y'] = closeData['trade_date'].apply(lambda x: x[:4])\n",
    "\n",
    "    benchmarkData = dv.data_benchmark\n",
    "    benchmarkData['trade_date'] = benchmarkData.index\n",
    "    benchmarkData['trade_date'] = benchmarkData['trade_date'].apply(lambda x: str(x))\n",
    "    benchmarkData['trade_y'] = benchmarkData['trade_date'].apply(lambda x: x[:4])\n",
    "\n",
    "    isIndexMember = dv.get_ts('index_member') == 1\n",
    "    isIndexMember['trade_date'] = isIndexMember.index\n",
    "    isIndexMember['trade_date'] = isIndexMember['trade_date'].apply(lambda x: str(x))\n",
    "    isIndexMember['trade_y'] = isIndexMember['trade_date'].apply(lambda x: x[:4])\n",
    "\n",
    "    # 筛选出成份股及指数年末收盘价\n",
    "    closeData_Y = closeData.groupby(['trade_y']).last()\n",
    "    closeData_Y = closeData_Y.drop('trade_date', 1)\n",
    "    benchmark_Y = benchmarkData.groupby(['trade_y']).last()\n",
    "    benchmark_Y = benchmark_Y.drop('trade_date', 1)\n",
    "    isIndexMember_Y = isIndexMember.groupby(['trade_y']).last()\n",
    "    isIndexMember_Y = isIndexMember_Y.drop('trade_date', 1)\n",
    "    isIndexMember_Y = isIndexMember_Y.iloc[:-1, :]\n",
    "\n",
    "    # 计算成份股及指数年收益率\n",
    "    ret_Y = closeData_Y.diff() / closeData_Y.shift(1) * 100\n",
    "    ret_Y_benchmark = benchmark_Y.diff() / benchmark_Y.shift(1) * 100\n",
    "\n",
    "    ret_Y = ret_Y.iloc[1:,:]\n",
    "    ret_Y_benchmark = ret_Y_benchmark.iloc[1:,:]\n",
    "\n",
    "    ret_Y_satisfied = pd.DataFrame(ret_Y.values * isIndexMember_Y.values)\n",
    "\n",
    "    # 计算成份股年收益率中位数\n",
    "    ret_Y_satisfied = ret_Y_satisfied.replace(0.0, np.nan)\n",
    "    ret_Y_satisfied['median_ret'] = ret_Y_satisfied.median(axis = 1)\n",
    "\n",
    "    outputRet = {'index_ret': ret_Y_benchmark['close'].tolist(), 'median_ret': ret_Y_satisfied['median_ret'].tolist()}\n",
    "    outputRet = pd.DataFrame.from_dict(outputRet)\n",
    "    outputRet.index = ret_Y_benchmark.index\n",
    "    \n",
    "    retCompare = pd.concat([retCompare, outputRet], axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>trade_y</th>\n",
       "      <th>2016</th>\n",
       "      <th>2017</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>SZ50_index</th>\n",
       "      <td>-5.531327</td>\n",
       "      <td>25.210936</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SZ50_median</th>\n",
       "      <td>-7.682435</td>\n",
       "      <td>10.946135</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HS300_index</th>\n",
       "      <td>-11.281784</td>\n",
       "      <td>21.027236</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HS300_median</th>\n",
       "      <td>-15.269830</td>\n",
       "      <td>1.906094</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZZ500_index</th>\n",
       "      <td>-17.775252</td>\n",
       "      <td>-0.003266</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZZ500_median</th>\n",
       "      <td>-18.092379</td>\n",
       "      <td>-9.616558</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ashare_index</th>\n",
       "      <td>-14.405746</td>\n",
       "      <td>2.647241</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ashare_median</th>\n",
       "      <td>-14.875100</td>\n",
       "      <td>-18.338202</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "trade_y             2016       2017\n",
       "SZ50_index     -5.531327  25.210936\n",
       "SZ50_median    -7.682435  10.946135\n",
       "HS300_index   -11.281784  21.027236\n",
       "HS300_median  -15.269830   1.906094\n",
       "ZZ500_index   -17.775252  -0.003266\n",
       "ZZ500_median  -18.092379  -9.616558\n",
       "Ashare_index  -14.405746   2.647241\n",
       "Ashare_median -14.875100 -18.338202"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "retCompare.columns = ['SZ50_index', 'SZ50_median', 'HS300_index', 'HS300_median', 'ZZ500_index', 'ZZ500_median']\n",
    "retCompare.T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
